<?
class experticia{

	// Propiedades
	var $id;
	var $id_tipo_exp;
	var $tipo_exp;
	var $costo_exp;
	var $pago_exp;
	var $acta_exp;
	var $fecha_exp;
	var $id_perito;
	var $codigo_perito;
	var $nombre_perito;
	var $id_conductor;
	var $letra_cli;
	var $cedula_cli;
	var $nombre_cli;
	var $letra_per;
	var $cedula_per;
	var $nombre_per;
	var $direccion_per;
	var $telefono_per;
	var $id_propietario;
	var $letra_per2;
	var $cedula_per2;
	var $nombre_per2;
	var $direccion_per2;
	var $telefono_per2;
	var $placa_veh;
	var $id_marca_veh;
	var $marca_veh;
	var $modelo_veh;
	var $id_modelo_veh;
	var $id_color_veh;
	var $color_veh;
	var $anio_veh;
	var $uso_veh;
	var $carroceria_veh;
	var $motor_veh;
	var $seguro_veh;
	var $poliza;
	var $fecha_poliza;
	var $titulo;
	var $traspasos;
	var $lugar_accidente;
	var $fecha_accidente;
	var $hora_accidente;
	var $lugar_revision;
	var $observaciones;
	var $cod_observaciones;
	var $texto_danos;
	var $texto_danos2;
	var $total_avaluo;
	var $estatus;
	var $foto1;
	var $foto2;
	var $foto3;
	var $danos;
	var $numero_factura;
	var $id_vehiculo;
	var $tipo_veh;
	var $id_zona;
	var $codigo_oficina;
	var $nombre_oficina;
	var $tipo_material;
	var $hoja_exp;
	var $motivo_anulacion;
	var $id_estado;	
	var $id_municipio;
	var $id_parroquia;
	var $total;
	var $id_factura;
	var $revisada;
	
	
	function get($conn, $id){
		try {
			$q = "SELECT 
					A.id_zona, A.id AS id_factura, B.id, B.id_tipo AS id_tipo_exp, I.nombre AS tipo_exp,  B.costo AS costo_exp, B.pago1 + B.pago2 AS pago_exp,
					COALESCE(B.acta::text,'') || '-' || G.codigo || '-' || to_char(B.fecha,'yy') AS acta_exp, B.fecha AS fecha_exp,
					B.id_perito, B.titulo, B.traspasos, B.id_conductor,
					C.letra AS letra_per, C.cedula AS cedula_per, C.nombre AS nombre_per,
					C.direccion AS direccion_per, C.telefono AS telefono_per,
					B.id_propietario, H.letra AS letra_per2, H.cedula AS cedula_per2, H.nombre AS nombre_per2,
					H.direccion AS direccion_per2, H.telefono AS telefono_per2,
					B.id_vehiculo,  D.id_tipo AS tipo_veh,
					CASE WHEN strpos(D.placa,'S/P')=1 THEN NULL ELSE D.placa END AS placa_veh, CASE WHEN D.placa IS NULL THEN 'S' ELSE 'N' END AS sinplaca_veh,
					E.id AS id_marca_veh, F.id AS id_modelo_veh,
					E.nombre AS marca_veh, F.nombre AS modelo_veh, D.id_color AS id_color_veh, L.nombre AS color_veh, D.anio AS anio_veh, 
					D.id_uso AS uso_veh, D.carroceria AS carroceria_veh, D.motor AS motor_veh,
					B.id_seguro AS seguro_veh, B.poliza, B.vence_poliza AS fecha_poliza, B.lugar_accidente, 
					B.fecha_accidente, B.hora_accidente, B.lugar_revision,
					B.avaluo AS total_avaluo, B.danos AS texto_danos , B.estatus,
					A.id AS id_factura, A.id_zona,lpad(A.numero::text, 10, '0') AS numero_factura, 
					J.letra AS letra_cli, J.cedula AS cedula_cli, J.nombre AS nombre_cli, B.tipo_material, B.hoja, 
					B.id_estado, B.id_municipio, B.id_parroquia, B.motivo_anulacion, B.completa, B.estatus, B.revisada,
					(SELECT ubicacion FROM fotos_experticia WHERE id_experticia = B.id AND ubicacion ILIKE 'images/exp/' || B.id || '-1%' LIMIT 1) AS foto1, 
					(SELECT ubicacion FROM fotos_experticia WHERE id_experticia = B.id AND ubicacion ILIKE 'images/exp/' || B.id || '-2%' LIMIT 1) AS foto2, 
					(SELECT ubicacion FROM fotos_experticia WHERE id_experticia = B.id AND ubicacion ILIKE 'images/exp/' || B.id || '-3%' LIMIT 1) AS foto3,
					array_to_string(ARRAY(SELECT Y.nombre FROM observaciones_experticia  AS X INNER JOIN observaciones AS Y ON X.id_observacion = Y.id WHERE X.id_experticia = B.id ),'. ') AS observaciones,
					M.codigo AS codigo_oficina, M.nombre AS nombre_oficina, G.codigo AS codigo_perito, G.nombre AS nombre_perito
				FROM facturas AS A 
					LEFT JOIN experticias AS B ON A.id_experticia = B.id
					LEFT JOIN personas AS C ON B.id_conductor = C.id
					LEFT JOIN vehiculos AS D ON B.id_vehiculo =D.id
					LEFT JOIN marcas AS E ON D.id_marca = E.id
					LEFT JOIN modelos AS F ON D.id_modelo = F.id
					LEFT JOIN peritos AS G ON B.id_perito = G.id
					LEFT JOIN personas AS H ON B.id_propietario = H.id
					LEFT JOIN tipos_experticia AS I ON B.id_tipo = I.id
					LEFT JOIN personas AS J ON A.id_persona = J.id
					LEFT JOIN colores AS L ON D.id_color = L.id
					LEFT JOIN zonas AS M ON A.id_zona = M.id
				WHERE B.id=COALESCE(NULLIF('$id',''),'0')::integer";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){

				$this->id = $r->fields['id'];
				$this->id_factura = $r->fields['id_factura'];
				$this->tipo_exp = $r->fields['tipo_exp'];
				$this->id_tipo_exp = $r->fields['id_tipo_exp'];
				$this->costo_exp = $r->fields['costo_exp'];
				$this->pago_exp = $r->fields['pago_exp'];
				$this->acta_exp = $r->fields['acta_exp'];
				$this->fecha_exp = $r->fields['fecha_exp'];
				$this->id_perito = $r->fields['id_perito'];
				$this->codigo_perito = $r->fields['codigo_perito'];
				$this->nombre_perito = $r->fields['nombre_perito'];
				$this->id_conductor = $r->fields['id_conductor'];
				$this->letra_per = $r->fields['letra_per'];
				$this->cedula_per = $r->fields['cedula_per'];
				$this->nombre_per = $r->fields['nombre_per'];
				$this->direccion_per = $r->fields['direccion_per'];
				$this->telefono_per = $r->fields['telefono_per'];
				$this->id_propietario = $r->fields['id_propietario'];
				$this->letra_per2 = $r->fields['letra_per2'];
				$this->cedula_per2 = $r->fields['cedula_per2'];
				$this->nombre_per2 = $r->fields['nombre_per2'];
				$this->direccion_per2 = $r->fields['direccion_per2'];
				$this->telefono_per2 = $r->fields['telefono_per2'];
				$this->placa_veh = $r->fields['placa_veh'];
				$this->id_marca_veh = $r->fields['id_marca_veh'];
				$this->marca_veh = $r->fields['marca_veh'];
				$this->id_modelo_veh = $r->fields['id_modelo_veh'];
				$this->modelo_veh = $r->fields['modelo_veh'];
				$this->id_color_veh = $r->fields['id_color_veh'];
				$this->color_veh = $r->fields['color_veh'];
				$this->anio_veh = $r->fields['anio_veh'];
				$this->uso_veh = $r->fields['uso_veh'];
				$this->carroceria_veh = $r->fields['carroceria_veh'];
				$this->motor_veh = $r->fields['motor_veh'];
				$this->seguro_veh = $r->fields['seguro_veh'];
				$this->poliza = $r->fields['poliza'];
				$this->fecha_poliza = $r->fields['fecha_poliza'];
				$this->titulo = $r->fields['titulo'];
				$this->traspasos = $r->fields['traspasos'];
				$this->lugar_accidente = $r->fields['lugar_accidente'];
				$this->fecha_accidente = $r->fields['fecha_accidente'];
				$this->hora_accidente = $r->fields['hora_accidente'];
				$this->lugar_revision = $r->fields['lugar_revision'];
				$this->observaciones = $r->fields['observaciones'];
				$this->texto_danos = $r->fields['texto_danos'];
				$this->total_avaluo = $r->fields['total_avaluo'];
				if ($r->fields['estatus']== 'R') {
					$this->estatus = $r->fields['completa']=="S"? "COMPLETA" : "PENDIENTE";
				} else {
					if($r->fields['estatus']== 'A') {
						$this->estatus = "ANULADA";
					}
				}
				$this->foto1 = $r->fields['foto1'];
				$this->foto2 = $r->fields['foto2'];
				$this->foto3 = $r->fields['foto3'];
				$this->numero_factura = $r->fields['numero_factura'];
				$this->letra_cli = $r->fields['letra_cli'];
				$this->cedula_cli = $r->fields['cedula_cli'];
				$this->nombre_cli = $r->fields['nombre_cli'];
				$this->id_vehiculo = $r->fields['id_vehiculo'];
				$this->tipo_veh = $r->fields['tipo_veh'];
				$this->id_zona = $r->fields['id_zona'];
				$this->codigo_oficina = $r->fields['codigo_oficina'];
				$this->nombre_oficina = $r->fields['nombre_oficina'];
				$this->tipo_material = $r->fields['tipo_material'];
				$this->hoja_exp = $r->fields['hoja'];
				$this->motivo_anulacion = $r->fields['motivo_anulacion'];
				$this->id_estado = $r->fields['id_estado'];
				$this->id_municipio = $r->fields['id_municipio'];
				$this->id_parroquia = $r->fields['id_parroquia'];
				$this->revisada = $r->fields['revisada'];
				
				
				$q = "SELECT B.codigo AS codigo_parte, 
						B.descripcion AS descripcion_parte, 
						C.codigo AS codigo_estado, 
						C.nombre  || CASE WHEN B.genero='M' THEN 'O' WHEN B.genero='F' THEN 'A' ELSE '' END
						|| CASE WHEN B.plural='S' THEN 'S' ELSE '' END	
						AS descripcion_estado,
						A.monto
					FROM danos_experticia AS A 
						INNER JOIN partes AS B ON A.id_parte = B.id
						INNER JOIN danos AS C ON A.id_dano = C.id 
					WHERE A.id_experticia=$this->id 
					ORDER BY C.nombre, B.descripcion";
				//die($q);
				$rCF = $conn->Execute(utf8_encode($q));
				$i=0;
				$temp_dano = "";
				$temp_texto_danos2 = "";

				while(!$rCF->EOF){
					$DanosAux[$i][0] = $rCF->fields['codigo_parte'];
					$DanosAux[$i][1] = $rCF->fields['descripcion_parte'];
					$DanosAux[$i][2] = $rCF->fields['codigo_estado'];
					$DanosAux[$i][3] = $rCF->fields['descripcion_estado'];
					$DanosAux[$i][4] = $rCF->fields['monto'];
					
					if ($temp_dano == "") {

						$temp_dano = $rCF->fields['descripcion_estado'];
						$temp_texto_danos2 = "PARTES O PIEZAS A " . $rCF->fields['descripcion_estado'] . ": " . $rCF->fields['descripcion_parte']; 
					} else {
						if  ($temp_dano == $rCF->fields['descripcion_estado']  ) {
							$temp_texto_danos2 = $temp_texto_danos2 . ", " . $rCF->fields['descripcion_parte'];
						} else {
							$temp_dano= $rCF->fields['descripcion_estado'];
							$temp_texto_danos2 = $temp_texto_danos2 . "." . chr(13) . " PARTES O PIEZAS A " . $rCF->fields['descripcion_estado'] . ": " . $rCF->fields['descripcion_parte'];
						}
					}
					$i++;
					$rCF->movenext();
				}

				if($temp_texto_danos2 <> "") {
					$temp_texto_danos2 = $temp_texto_danos2 . ".";
				}
 				$this->texto_danos2 = $temp_texto_danos2;

				$this->danos = new Services_JSON();
				$this->danos = is_array($DanosAux) ? $this->danos->encode($DanosAux) : false;


				$q = "SELECT B.codigo AS codigo_observacion, 
						 B.nombre AS descripcion_observacion
					FROM observaciones_experticia AS A 
						INNER JOIN observaciones AS B ON A.id_observacion = B.id
					WHERE A.id_experticia=$this->id 
					ORDER BY B.codigo, B.nombre";
				//die($q);
				$rCF = $conn->Execute(utf8_encode($q));
				$i=0;

				while(!$rCF->EOF){
					$ObservacionesAux[$i][0] = $rCF->fields['codigo_observacion'];
					$ObservacionesAux[$i][1] = $rCF->fields['descripcion_observacion'];
					
					$i++;
					$rCF->movenext();
				}

				$this->cod_observaciones = new Services_JSON();
				$this->cod_observaciones = is_array($ObservacionesAux) ? $this->cod_observaciones->encode($ObservacionesAux) : false;
				
				
			}
			 
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function get_all($conn, $orden="A.id DESC", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {
			$zona = $_SESSION['SesionZona'];
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.="INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "WHERE id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.="INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "WHERE A.id::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==1){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.="INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "WHERE B.numero::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN personas AS C ON B.id_persona = C.id ";
			$q.= "WHERE C.cedula::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.="INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN personas AS C ON B.id_persona = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==4){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN tipos_experticia AS C ON A.id_tipo = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==5){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.="INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN vehiculos AS C ON A.id_vehiculo = C.id ";
			$q.= "WHERE C.placa::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			//die($q);
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new experticia;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function get_all_global($conn, $orden="A.id DESC", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {
			$zona = $_SESSION['SesionZona'];
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==1){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE B.numero::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN personas AS D ON B.id_persona = D.id ";
			$q.= "WHERE D.cedula::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.="INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN personas AS D ON B.id_persona = D.id ";
			$q.= "WHERE D.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==4){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN tipos_experticia AS D ON A.id_tipo = D.id ";
			$q.= "WHERE D.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==5){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN vehiculos AS D ON A.id_vehiculo = D.id ";
			$q.= "WHERE D.placa::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==6){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE C.codigo::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}			
			elseif($Tipo==7){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN peritos AS C ON A.id_perito = C.id ";
			$q.= "WHERE C.codigo::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}			
			elseif($Tipo==8){
			$q = "SELECT A.id FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN peritos AS C ON A.id_perito = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			$q.= "ORDER BY $orden ";
			}			
			//die($q);
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new experticia;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function __destruct(){
    }
	

	function set($conn, $id, $id_tipo_exp, $tipo_exp, $costo_exp, $acta_exp, $fecha_exp, $id_perito, $id_conductor, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $id_propietario, $cedula_per2, $nombre_per2, $direccion_per2, $telefono_per2, $placa_veh, $id_marca_veh, $marca_veh, $modelo_veh, $id_modelo_veh, $id_color_veh, $anio_veh, $uso_veh, $carroceria_veh, $motor_veh, $seguro_veh, $poliza, $fecha_poliza, $titulo, $traspasos, $lugar_accidente, $fecha_accidente, $hora_accidente, $lugar_revision, $observaciones, $texto_danos, $total_avaluo, $foto1, $foto2, $foto3, $danos, $numero_factura, $cedula_cli, $nombre_cli, $id_vehiculo, $tipo_veh, $tipo_material, $hoja_exp, $id_estado, $id_municipio, $id_parroquia, $letra_per, $letra_per2, $motivo_anulacion, $elimina_foto1, $elimina_foto2, $elimina_foto3, $cod_observaciones, $revisada){
		try {
			//Verificar Existencia Persona
			$opersona = new persona;
			$q = "SELECT * FROM personas WHERE letra= '$letra_per' AND cedula = '$cedula_per'";
			$r = $conn->Execute(utf8_encode($q));
			if (!$r->EOF) {
				$opersona->set($conn, $r->fields['id'], $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$id_per = $r->fields['id'];
			} else {
				$opersona->add($conn, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$q = "SELECT * FROM personas WHERE letra = '$letra_per' AND cedula = '$cedula_per'";
				$r = $conn->Execute(utf8_encode($q));
				if (!$r->EOF) {
					$id_per = $r->fields['id'];
				}

			}

			//Verificar Existencia Persona2
			$opersona = new persona;

			$q = "SELECT * FROM personas WHERE letra = '$letra_per2' AND cedula = '$cedula_per2'";
			$r = $conn->Execute(utf8_encode($q));
			if (!$r->EOF) {
				$opersona->set($conn, $r->fields['id'], $cedula_per2, $nombre_per2, $direccion_per2, $telefono_per2, $letra_per2);
				$id_per2 = $r->fields['id'];
			} else {
				$opersona->add($conn, $cedula_per2, $nombre_per2, $direccion_per2, $telefono_per2, $letra_per2);
				$q = "SELECT * FROM personas WHERE letra = '$letra_per2' AND cedula = '$cedula_per2'";
				$r = $conn->Execute(utf8_encode($q));
				if (!$r->EOF) {
					$id_per2 = $r->fields['id'];
				}

			}
			

			//Actualizar Datos Vehiculo
			$ovehiculo = new vehiculo;
			$ovehiculo->set($conn, $id_vehiculo, $placa_veh, $id_marca_veh, $id_modelo_veh, $tipo_veh, $id_color_veh, $anio_veh, $uso_veh, $carroceria_veh, $motor_veh);
			
			$otipo_exp = new tipos_experticia;
			$otipo_exp->get($conn,$id_tipo_exp);
			$pago_exp = guardafloat($otipo_exp->pago);

			//UPDATE Experticia
			$q = "UPDATE experticias SET id_tipo = $id_tipo_exp, costo = $costo_exp, fecha= '$fecha_exp', id_perito = NULLIF($id_perito,-1)::integer, titulo= upper('$titulo'), traspasos = NULLIF('$traspasos','-1'), id_conductor= $id_per, id_propietario= $id_per2, id_vehiculo=$id_vehiculo, id_seguro = $seguro_veh, poliza = upper('$poliza'), vence_poliza = '$fecha_poliza', lugar_accidente =  upper('$lugar_accidente'), fecha_accidente = '$fecha_accidente', avaluo= $total_avaluo, danos = upper('$texto_danos'), hora_accidente = '$hora_accidente', lugar_revision = upper('$lugar_revision'), tipo_material = NULLIF('$tipo_material','-1')::integer, hoja = NULLIF('$hoja_exp','')::integer, id_estado = NULLIF('$id_estado','-1')::integer, id_municipio = NULLIF('$id_municipio','-1')::integer, id_parroquia = NULLIF('$id_parroquia','-1')::integer, motivo_anulacion = upper('$motivo_anulacion'), revisada=upper('$revisada') ";
			$q.= "WHERE id = $id";
			
			$this->auditoria($conn, $q);
			
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			
			$this->GuardarDanos($conn, $id, $danos);
			$this->GuardarObservaciones($conn, $id, $cod_observaciones);
			
			if($elimina_foto1=="on") {
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				
			}
			
			if(!empty($foto1)){

				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}

				$q="INSERT INTO fotos_experticia(id_experticia, ubicacion) VALUES ($id,'$foto1');";
				$conn->Execute($q);
				
				$this->auditoria($conn, $q);
			}

			if($elimina_foto2=="on") {
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				
				
			}

			if(!empty($foto2)){
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}
				
				$q="INSERT INTO fotos_experticia(id_experticia, ubicacion) VALUES ($id,'$foto2');";
				$conn->Execute($q);
				
				$this->auditoria($conn, $q);
			}

			if($elimina_foto3=="on") {
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				
				
			}

			if(!empty($foto3)){
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				

				$q="INSERT INTO fotos_experticia(id_experticia, ubicacion) VALUES ($id,'$foto3');";
				$conn->Execute($q);
				
				$this->auditoria($conn, $q);
			}
			
			$q="SELECT actualiza_exp_completa($id);";
			$conn->Execute($q);

			return REG_SET_OK;

		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function set_danos_fotos($conn, $id, $observaciones, $texto_danos, $total_avaluo, $foto1, $foto2, $foto3, $danos, $elimina_foto1, $elimina_foto2, $elimina_foto3, $cod_observaciones, $revisada){
		try {

			//UPDATE Experticia
			$q = "UPDATE experticias SET avaluo= $total_avaluo, danos = upper('$texto_danos'), revisada = upper('$revisada') ";
			$q.= "WHERE id = $id";
			
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			$this->GuardarDanos($conn, $id, $danos);
			$this->GuardarObservaciones($conn, $id, $cod_observaciones);

			if($elimina_foto1=="on") {
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				
			}
			
			if(!empty($foto1)){

				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-1%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}

				$q="INSERT INTO fotos_experticia(id_experticia, ubicacion) VALUES ($id,'$foto1');";
				$conn->Execute($q);
				
				$this->auditoria($conn, $q);
			}

			if($elimina_foto2=="on") {
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				
				
			}

			if(!empty($foto2)){
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-2%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}
				
				$q="INSERT INTO fotos_experticia(id_experticia, ubicacion) VALUES ($id,'$foto2');";
				$conn->Execute($q);
				$this->auditoria($conn, $q);
			}

			if($elimina_foto3=="on") {
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				
				
			}

			if(!empty($foto3)){
				//$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%' RETURNING ubicacion;";
				$q="DELETE FROM fotos_experticia WHERE id_experticia = $id AND ubicacion ILIKE 'images/exp/$id-3%';";
				$r = $conn->Execute($q);
				
				if (!$r->EOF){
					$this->auditoria($conn, $q);
					unlink($r->fields['ubicacion']);
				}				

				$q="INSERT INTO fotos_experticia(id_experticia, ubicacion) VALUES ($id,'$foto3');";
				$conn->Execute($q);
				
				$this->auditoria($conn, $q);
			}
			
			$q="SELECT actualiza_exp_completa($id);";
			$conn->Execute($q);

			return REG_SET_OK;

		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}


	function GuardarDanos($conn,$id,$danos){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$danos));
		
			$q = "DELETE FROM danos_experticia WHERE id_experticia=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->danos)){
				foreach($JsonRec->danos as $danosAux){
					$monto = guardafloat($danosAux[4]);
					$q = "INSERT INTO danos_experticia ";
					$q.= "(id_experticia, id_parte, id_dano, monto) ";
					$q.= "VALUES ";
					$q.= "($id, (SELECT id FROM partes WHERE codigo = '$danosAux[0]' LIMIT 1), (SELECT id FROM danos WHERE codigo = '$danosAux[2]' LIMIT 1), $monto); ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarObservaciones($conn,$id,$observaciones){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$observaciones));
			$q = "DELETE FROM observaciones_experticia WHERE id_experticia=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->cod_observaciones)){
				foreach($JsonRec->cod_observaciones as $observacionesAux){
					$q = "INSERT INTO observaciones_experticia ";
					$q.= "(id_experticia, id_observacion) ";
					$q.= "VALUES ";
					$q.= "($id, (SELECT id FROM observaciones WHERE codigo = '$observacionesAux[0]' LIMIT 1)); ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="A.id")
	{
		$zona = $_SESSION['SesionZona'];
		$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "WHERE B.id_zona = $zona ";
			}
			elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "WHERE B.id::text ILIKE '%$Valor%'::text AND id_zona = $zona ";
			}
			elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "WHERE B.numero::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			}
			elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN personas AS C ON B.id_persona = C.id ";
			$q.= "WHERE C.cedula::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			}
			elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN personas AS C ON B.id_persona = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			}
			elseif($Tipo==4){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN tipos_experticia AS C ON A.id_tipo = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			}
			elseif($Tipo==5){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN vehiculos AS C ON A.id_vehiculo = B.id ";
			$q.= "WHERE C.placa::text ILIKE '%$Valor%'::text AND B.id_zona = $zona ";
			}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}	
	function total_registro_busqueda_global($conn,$Tipo, $Valor, $TipoE, $orden="A.id")
	{
		$zona = $_SESSION['SesionZona'];
		$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE A.completa = 'S' ";
			}
			elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE B.numero::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN personas AS D ON B.id_persona = D.id ";
			$q.= "WHERE D.cedula::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN personas AS D ON B.id_persona = D.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==4){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN tipos_experticia AS D ON A.id_tipo = D.id ";
			$q.= "WHERE D.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==5){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "INNER JOIN vehiculos AS D ON A.id_vehiculo = D.id ";
			$q.= "WHERE D.placa::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==6){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= "WHERE C.codigo::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}
			elseif($Tipo==7){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN peritos AS C ON A.id_perito = C.id ";
			$q.= "WHERE C.codigo::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}			
			elseif($Tipo==8){
			$q = "SELECT count(*) AS total FROM experticias AS A ";
			$q.= "INNER JOIN facturas AS B ON A.id = B.id_experticia ";
			$q.= "INNER JOIN peritos AS C ON A.id_perito = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text AND A.completa = 'S' ";
			}			
		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}

	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}
	
}
?>
